#!/bin/bash
##################################################################################################
##  Purpose:postgresql install 
##  Author :syd
##  Created:2024-06-08
##  Version:1
##################################################################################################
# 1.日志输出格式
log(){
	LOGFILE=install_pg_$(date +"%Y%m%d").log
	LOGTYPE=$1
	NOW_TIME='['$(date +"%Y-%m-%d %H:%M:%S")']'
	case $LOGTYPE in
	ERROR)
	    echo -e "\033[31m$NOW_TIME [$1]$2\033[0m" 2>&1 | tee -a $LOGFILE;;
	INFO)
	    echo -e "\033[34m$NOW_TIME [$1]$2\033[0m" 2>&1 | tee -a $LOGFILE;;
	INPUT)
	    echo -e "\033[33m$NOW_TIME [$1]$2\033[0m" 2>&1 | tee -a $LOGFILE;;
	CMD)
		echo -e -n "\033[37m$NOW_TIME $2\n\033[0m" 2>&1 | tee -a $LOGFILE
		echo -e -n "\033[37m `$2`\n\033[0m" 2>&1 | tee -a $LOGFILE;;
	esac
}

# 2.磁盘环境检查
check_disk(){
	log CMD 'df -Th'
	log CMD 'fdisk -l'
	while [[ ! $t =~ [y,Y,n,N] ]]
	do
	read -p "是否需要挂载新磁盘【默认n】：" t
	log INFO "是否需要挂载新磁盘【默认n】，你选择${t:=n}。"
	case "$t" in
		Y|y)
			while [[ $cmd1 -eq 0 ]]
			do
			read -p "需要挂载的磁盘路径为【默认/dev/sdb】：" d
			log INFO "你选择挂载的磁盘路径为 ${d:=/dev/sdb}"
			cmd1=`fdisk -l|grep $d|wc -l`
			if [ $cmd1 -eq 0 ]
				then
					log ERROR "磁盘不存在，请重新输入"
			else
				log INFO "开始挂载磁盘……"
				fdisk $d
				mkfs.xfs ${d}1
				UUID=`blkid|grep ${d}1|awk -F '"' '{print $2}'`
				
				if [ `grep $UUID /etc/fstab|wc -l` -eq 0 ]
					then
						echo "UUID=$UUID    /data     xfs    defaults  0 0" >> /etc/fstab
					else
						:
				fi
				
				if [ -d /data ]
				then
						:
				else
						mkdir -p /data
				fi
				mount -a
				df -Th
				log INFO "挂载磁盘成功。"
			fi
			done
			;;
		N|n)
			:
			;;
		*)
			log ERROR "只可输入n或y，请重新输入！"
	esac
	done
}

# 3.定义变量
def_var(){
	groupadd postgres
	useradd -g postgres postgres
	echo 'postgres:postgres' |chpasswd
	rpm -ivh epel-release-7-14.noarch.rpm
	yum install -y libzstd libzstd.so.1 libpython3.6m.so.1.0 libzstd.so.1
	rpm -ivh postgresql*
	PGHOME=`find /usr -name pgsql-*`
	read -p "数据库数据目录【默认:/data/postgresql/data】: " BPGDATA
	log INFO "你选择的数据库数据目录为：${BPGDATA:=/data/postgresql/data}"
	read -p "数据库端口【默认:5432】: " PORT
	log INFO "你选择的数据库端口为：${PORT:=5432}"
	read -p "数据库备份目录【默认:/data/postgresql/pgbak】: " BACK_DIR
	log INFO "你选择的数据库备份目录为：${BACK_DIR:=/data/postgresql/pgbak}"
	read -p "数据库密码【默认:P0sTsgr@121】: " PASSWORD
	log INFO "你将设置的数据库密码为：${PASSWORD:=P0sTsgr@121}"
	
	if [ -d $BACK_DIR ]
	then
			:
	else
		mkdir -p $BACK_DIR/{archive,dbbak}
		chown postgres:postgres $BACK_DIR/.. -R
	fi	
	
	if [ `grep pgsql /home/postgres/.bash_profile|wc -l` -eq 0 ]
		then
			echo "export PGHOME=$PGHOME" >> /home/postgres/.bash_profile
			echo "export PGUSER=postgres" >> /home/postgres/.bash_profile
			echo "export PGPORT=$PORT" >> /home/postgres/.bash_profile
			echo "export PGDATA=$BPGDATA" >> /home/postgres/.bash_profile
			echo "export PATH=$PGHOME/bin:\$PATH" >> /home/postgres/.bash_profile
			echo "export LD_LIBRARY_PATH=$PGHOME/lib:\$LD_LIBRARY_PATH" >> /home/postgres/.bash_profile
	else
		:
	fi
	
	source /home/postgres/.bash_profile
}



# 4.初始化数据库
init(){
        echo "$PASSWORD" > /tmp/.default_passwd
        log INFO "开始初始化数据库实例……"
        su - postgres -c "initdb -D $BPGDATA -A scram-sha-256 --pwfile=/tmp/.default_passwd"
        if [ $? -ne 0 ]; then
                rm -rf /tmp/.default_passwd
                exit
        fi
        rm -rf /tmp/.default_passwd
        log INFO "初始化数据库实例成功。"
}


# 5.数据库参数配置
hgfile_set(){
log INFO "开始配置数据库参数……"
OS_MEM=`grep MemTotal /proc/meminfo | awk '{print $2}'`
let HG_MEM=$OS_MEM/1024/4

cat >> $BPGDATA/postgresql.auto.conf <<EOF
listen_addresses = '*'
port = '$PORT'
max_connections = 1000
shared_buffers = '${HG_MEM}MB' 
checkpoint_completion_target = 0.8
log_destination = 'csvlog'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresqldb_%d.log'
log_rotation_age = '1d'
log_rotation_size = 0
log_truncate_on_rotation = on
log_statement = 'ddl'
log_connections=on
log_disconnections=on
checkpoint_timeout='30min'
maintenance_work_mem='2GB'    
min_wal_size ='800MB'
max_wal_size ='3200MB'
archive_mode = on
archive_command = ' cp %p ${BACK_DIR}/archive/%f'
archive_timeout = '30min'
EOF

echo 'host    all             all             0.0.0.0/0               scram-sha-256'>>$BPGDATA/pg_hba.conf

log INFO "数据库参数配置成功。"

su - postgres -c "pg_ctl start -D $BPGDATA"
if [ $? = 0 ]
then
	log INFO "数据库服务启动成功，运行状态正常！" 
else
	log ERROR "数据库服务启动失败，请检查启动的相关错误！"
fi
}

# 6.数据库开机自启配置
db_auto_statr(){
	sed -i "s#^Environment=PGDATA.*#Environment=PGDATA=$BPGDATA#" $(find /usr -name postgresql*.service)
	systemctl enable $(basename $(find /usr -name postgresql*.service))
	systemctl is-enabled $(basename $(find /usr -name postgresql*.service))
}

# 7. 免密登录设置
login_set(){
if [ -f /home/postgres/.pgpass ]
	then
		:
else
cat > /home/postgres/.pgpass <<EOF
#hostname:port:database:username:password
localhost:$PORT:postgres:postgres:$PASSWORD
EOF
fi

chown postgres. /home/postgres/.pgpass
chmod 600 /home/postgres/.pgpass
}

# 8. 添加定时任务
cronadd(){
log INFO "开始部署定时备份任务……"
if [ -f $PGHOME/pg_backup.sh ]
	then
		:
	else
		chown postgres. pg_backup.sh
		cp pg_backup.sh $PGHOME/
fi

cat > /var/spool/cron/postgres <<EOF
10 00  * * *  sh  $PGHOME/pg_backup.sh
EOF

su - postgres -c "crontab -l"
log INFO "定时备份任务部署成功。"

su - postgres -c "pg_ctl restart"

log INPUT "*************************************************************************" 
log INPUT "***********恭喜，数据库安装部署完成！请检查备份定时任务等。**************" 
log INPUT "*************************************************************************" 
}

# 9.主函数
main(){
	log
	check_disk
	def_var
	init
	hgfile_set
	db_auto_statr
	login_set
	cronadd
} 

# 10.执行
main
